if exists (select 1 from sysobjects where name = 'get_personenwithoutdienst' and type = 'P')
begin
   drop procedure get_personenwithoutdienst
   print 'Procedure: get_personenwithoutdienst deleted ...'
end
go
create procedure get_personenwithoutdienst(
  @planid            int      = 74,
  @abteilungid       int      = 1,
  @date              char(8)  = '20181001'
)
as
begin
  set nocount on
  
  --declare @prs table (prsid int)
  
 --insert @prs
 select prsid = ps.PrsID
   from PlanSchema ps
  inner join ArbeitsPlan ap
     on ap.SchemaID = ps.SchemaID
  inner join PersonalAbteilung pa
     on pa.PrsId = ps.PrsID
    and pa.AbteilungID = ps.AbteilungID
    and pa.Von < CONVERT(datetime,@date,112)
    and pa.Bis > CONVERT(datetime,@date,112)
    and isnull(pa.Status,1) < 2
   left outer join Arbeitszeit az
     on az.PrsID = ps.PrsID
    and az.AbteilungID = ps.AbteilungID
    and az.PlanID = ap.PlanID
    --and az.Haben < az.Soll
  inner join PersonAnstellung pan
     on pan.PrsID = ps.PrsID
    and pan.Von < CONVERT(datetime,@date,112)
    and pan.Bis > CONVERT(datetime,@date,112)
  inner join Anstellung ans
     on ans.AnstellungID = pan.AnstellungID
  where ap.PlanID = @planid
    and ps.PrsID not in (select PrsID from Dienstplan 
                                  where PlanID = @planid 
                                    and AbteilungID = @abteilungid 
                                    and Datum = CONVERT(datetime,@date,112))
    and ps.AbteilungID = @abteilungid
    and ((az.Haben < az.Soll) or az.Haben is null or az.Soll is null)
    and ans.Gerechnet = 1
    ----and ans.AnstellungID not in (3)
    --and ans.ausgelernt = 1
  order by ps.PrsID
 
 --select prsid from @prs
 
 
end
go
print 'Procedure: get_personenwithoutdienst done ...'
go

grant exec on get_personenwithoutdienst to prsadmins with grant option
go
grant exec on get_personenwithoutdienst to prsusers
go

